# /lab_booking/models.py
from sqlalchemy import (Column, Integer, String, TIMESTAMP, TEXT, DECIMAL,
                        BOOLEAN, DATETIME, Enum, ForeignKey)
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from .database import Base

class DeviceCategory(Base):
    __tablename__ = "device_categories"
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    name = Column(String(100), unique=True, nullable=False)
    created_at = Column(TIMESTAMP, server_default=func.now())
    devices = relationship("Device", back_populates="category")

class Device(Base):
    __tablename__ = "devices"
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    model = Column(String(255))
    category_id = Column(Integer, ForeignKey("device_categories.id"))
    image_url = Column(String(512))
    total_quantity = Column(Integer, nullable=False, default=1)
    is_reservable = Column(BOOLEAN, default=True)
    hourly_rate = Column(DECIMAL(10, 2), nullable=False, default=0.00)
    remarks = Column(TEXT)
    created_at = Column(TIMESTAMP, server_default=func.now())
    category = relationship("DeviceCategory", back_populates="devices")
    reservations = relationship("Reservation", back_populates="device") # 这个关系是 Device -> Reservation

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    openid = Column(String(128), unique=True, nullable=False, index=True)
    name = Column(String(100), nullable=True)
    avatar_url = Column(String(512), nullable=True)
    phone_number = Column(String(20), nullable=True)
    research_group = Column(String(255), nullable=True)
    created_at = Column(TIMESTAMP, server_default=func.now())
    
    # 反向关系：一个 User 创建了多个 Reservation
    created_reservations = relationship("Reservation", back_populates="creator")
    usage_records = relationship("UsageRecord", back_populates="actual_user")

class Reservation(Base):
    __tablename__ = "reservations"
    id = Column(Integer, primary_key=True, autoincrement=True)
    device_id = Column(Integer, ForeignKey("devices.id"), nullable=False)
    
    # 正向关系：一个 Reservation 对应一个创建者(User)
    creator_user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    creator = relationship("User", back_populates="created_reservations")

    # 新增的冗余字段
    contact_name = Column(String(100), nullable=False)
    contact_phone = Column(String(20), nullable=False)
    contact_group = Column(String(255))

    start_time = Column(DATETIME, nullable=False)
    end_time = Column(DATETIME, nullable=False)
    status = Column(Enum('confirmed', 'ongoing', 'completed', 'user_cancelled', 'system_cancelled', name='reservation_status_final'), nullable=False, default='confirmed')
    remarks = Column(TEXT)
    created_at = Column(TIMESTAMP, server_default=func.now())
    
    device = relationship("Device", back_populates="reservations")
    usage_record = relationship("UsageRecord", back_populates="reservation", uselist=False)

class UsageRecord(Base):
    __tablename__ = "usage_records"
    id = Column(Integer, primary_key=True, autoincrement=True)
    reservation_id = Column(Integer, ForeignKey("reservations.id"), unique=True, nullable=False)
    actual_user_id = Column(Integer, ForeignKey("users.id"))
    actual_user_name = Column(String(100), nullable=True)
    actual_user_phone = Column(String(20), nullable=True)
    actual_user_group = Column(String(255), nullable=True)
    actual_start_time = Column(DATETIME)
    actual_end_time = Column(DATETIME)
    total_minutes_used = Column(Integer)
    remarks = Column(TEXT)
    created_at = Column(TIMESTAMP, server_default=func.now())
    
    reservation = relationship("Reservation", back_populates="usage_record")
    actual_user = relationship("User", back_populates="usage_records")
    companions = relationship("Companion", back_populates="usage_record")

class Companion(Base):
    __tablename__ = "companions"
    id = Column(Integer, primary_key=True, autoincrement=True)
    usage_record_id = Column(Integer, ForeignKey("usage_records.id"), nullable=False)
    name = Column(String(100), nullable=False)
    phone_number = Column(String(20))
    created_at = Column(TIMESTAMP, server_default=func.now())
    
    usage_record = relationship("UsageRecord", back_populates="companions")